import os
import sqlite3
import datetime
from typing import List, Tuple, Dict, Optional, Any
from sqlite3 import Row

# 导入配置和工具
from config.constants import DB_NAME, DEFAULT_LOG_LEVEL
from utils.helper import logger, calculate_next_run


# -------------------------- 基础工具 --------------------------
def get_db_connection() -> sqlite3.Connection:
    """获取数据库连接（统一配置，支持按列名访问）"""
    try:
        # 确保数据库目录存在
        db_dir = os.path.dirname(DB_NAME)
        if not os.path.exists(db_dir):
            os.makedirs(db_dir)

        conn = sqlite3.connect(DB_NAME, check_same_thread=False)
        conn.row_factory = sqlite3.Row  # 支持row['column_name']访问
        conn.execute("PRAGMA foreign_keys = ON")  # 启用外键约束
        return conn
    except sqlite3.Error as e:
        logger.error(f"数据库连接失败：{str(e)}")
        raise  # 抛出异常，让调用方处理


def close_db_connection(conn: sqlite3.Connection) -> None:
    """关闭数据库连接"""
    if conn:
        try:
            conn.close()
        except sqlite3.Error as e:
            logger.error(f"数据库连接关闭失败：{str(e)}")


# -------------------------- 数据库初始化 --------------------------
def init_database() -> bool:
    """初始化数据库表结构（脚本表、任务表、日志表+触发器）"""
    conn = None
    try:
        conn = get_db_connection()
        cursor = conn.cursor()

        # 1. 脚本表（存储Python脚本信息）
        cursor.execute('''
        CREATE TABLE IF NOT EXISTS scripts (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT NOT NULL,
            path TEXT NOT NULL UNIQUE,
            description TEXT,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            last_modified TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )
        ''')

        # 2. 任务表（存储定时任务信息）
        # 关键修改：直接使用标准日志级别作为默认值，避免#等特殊字符
        cursor.execute('''
        CREATE TABLE IF NOT EXISTS tasks (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT NOT NULL,
            script_id INTEGER NOT NULL,
            cron_expression TEXT NOT NULL,
            is_enabled INTEGER DEFAULT 1,
            record_screen INTEGER DEFAULT 1,
            log_level TEXT DEFAULT 'INFO',  
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            last_run TIMESTAMP,
            next_run TIMESTAMP,
            FOREIGN KEY (script_id) REFERENCES scripts (id) ON DELETE CASCADE
        )
        ''')

        # 3. 任务运行日志表（存储任务执行记录）
        cursor.execute('''
        CREATE TABLE IF NOT EXISTS task_logs (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            task_id INTEGER NOT NULL,
            start_time TIMESTAMP NOT NULL,
            end_time TIMESTAMP,
            status TEXT NOT NULL,
            log_content TEXT,
            screen_recording_path TEXT,
            FOREIGN KEY (task_id) REFERENCES tasks (id) ON DELETE CASCADE
        )
        ''')

        # 4. 触发器：脚本修改时自动更新last_modified
        cursor.execute('''
        CREATE TRIGGER IF NOT EXISTS update_script_modtime
        AFTER UPDATE ON scripts
        FOR EACH ROW
        BEGIN
            UPDATE scripts SET last_modified = CURRENT_TIMESTAMP WHERE id = old.id;
        END
        ''')

        conn.commit()
        logger.info("✅ 数据库初始化成功（表结构已创建/验证）")
        return True
    except sqlite3.Error as e:
        logger.error(f"❌ 数据库初始化失败：{str(e)}")
        if conn:
            conn.rollback()
        return False
    finally:
        close_db_connection(conn)


# -------------------------- 脚本表操作（CRUD） --------------------------
def add_script(name: str, path: str, description: str = "") -> Tuple[bool, Optional[int]]:
    """
    添加脚本到数据库
    返回：(是否成功, 脚本ID/None)
    """
    conn = None
    try:
        conn = get_db_connection()
        cursor = conn.cursor()

        # 检查脚本路径是否已存在
        cursor.execute("SELECT id FROM scripts WHERE path = ?", (path,))
        if cursor.fetchone():
            logger.warning(f"脚本路径已存在：{path}")
            return False, None

        # 插入脚本记录
        cursor.execute('''
        INSERT INTO scripts (name, path, description)
        VALUES (?, ?, ?)
        ''', (name, path, description))

        conn.commit()
        script_id = cursor.lastrowid
        logger.info(f"添加脚本成功：script_id={script_id}，名称={name}，路径={path}")
        return True, script_id
    except sqlite3.Error as e:
        logger.error(f"添加脚本失败：{str(e)}，名称={name}，路径={path}")
        if conn:
            conn.rollback()
        return False, None
    finally:
        close_db_connection(conn)


def get_script_by_id(script_id: int) -> Optional[Dict[str, Any]]:
    """通过ID获取脚本信息"""
    conn = None
    try:
        conn = get_db_connection()
        cursor = conn.cursor()

        cursor.execute('''
        SELECT id, name, path, description, created_at, last_modified
        FROM scripts WHERE id = ?
        ''', (script_id,))

        script = cursor.fetchone()
        return dict(script) if script else None
    except sqlite3.Error as e:
        logger.error(f"获取脚本失败：script_id={script_id}，错误：{str(e)}")
        return None
    finally:
        close_db_connection(conn)


def get_script_by_path(path: str) -> Optional[Dict[str, Any]]:
    """通过路径获取脚本信息（路径唯一）"""
    conn = None
    try:
        conn = get_db_connection()
        cursor = conn.cursor()

        cursor.execute('''
        SELECT id, name, path, description, created_at, last_modified
        FROM scripts WHERE path = ?
        ''', (path,))

        script = cursor.fetchone()
        return dict(script) if script else None
    except sqlite3.Error as e:
        logger.error(f"获取脚本失败：路径={path}，错误：{str(e)}")
        return None
    finally:
        close_db_connection(conn)


def load_scripts() -> List[Dict[str, Any]]:
    """加载所有脚本（按名称排序）"""
    conn = None
    try:
        conn = get_db_connection()
        cursor = conn.cursor()

        cursor.execute('''
        SELECT id, name, path, description, created_at, last_modified
        FROM scripts ORDER BY name ASC
        ''')

        scripts = [dict(row) for row in cursor.fetchall()]
        logger.info(f"加载脚本数量：{len(scripts)}")
        return scripts
    except sqlite3.Error as e:
        logger.error(f"加载脚本失败：{str(e)}")
        return []
    finally:
        close_db_connection(conn)


def update_script(script_id: int, name: Optional[str] = None, description: Optional[str] = None) -> bool:
    """更新脚本信息（仅支持名称和描述）"""
    conn = None
    try:
        conn = get_db_connection()
        cursor = conn.cursor()

        # 构建更新字段
        update_fields = []
        params = []
        if name is not None:
            update_fields.append("name = ?")
            params.append(name)
        if description is not None:
            update_fields.append("description = ?")
            params.append(description)

        if not update_fields:
            logger.warning(f"无更新字段：script_id={script_id}")
            return True  # 无更新视为成功

        # 执行更新
        params.append(script_id)
        cursor.execute(f'''
        UPDATE scripts SET {', '.join(update_fields)}
        WHERE id = ?
        ''', params)

        if cursor.rowcount == 0:
            logger.warning(f"脚本不存在或无变化：script_id={script_id}")
            return False

        conn.commit()
        logger.info(f"更新脚本成功：script_id={script_id}，字段={update_fields}")
        return True
    except sqlite3.Error as e:
        logger.error(f"更新脚本失败：script_id={script_id}，错误：{str(e)}")
        if conn:
            conn.rollback()
        return False
    finally:
        close_db_connection(conn)


def delete_script(script_id: int) -> bool:
    """删除脚本（级联删除关联任务和日志）"""
    conn = None
    try:
        conn = get_db_connection()
        cursor = conn.cursor()

        # 先获取脚本名称（用于日志）
        cursor.execute("SELECT name FROM scripts WHERE id = ?", (script_id,))
        script = cursor.fetchone()
        script_name = script['name'] if script else f"ID={script_id}"

        # 执行删除（外键约束会自动删除关联任务和日志）
        cursor.execute("DELETE FROM scripts WHERE id = ?", (script_id,))

        if cursor.rowcount == 0:
            logger.warning(f"脚本不存在：script_id={script_id}")
            return False

        conn.commit()
        logger.info(f"删除脚本成功：script_id={script_id}，名称={script_name}")
        return True
    except sqlite3.Error as e:
        logger.error(f"删除脚本失败：script_id={script_id}，错误：{str(e)}")
        if conn:
            conn.rollback()
        return False
    finally:
        close_db_connection(conn)


# -------------------------- 任务表操作（CRUD） --------------------------
def add_task(
        name: str, script_id: int, cron_expression: str,
        is_enabled: bool = True, record_screen: bool = True,
        log_level: str = DEFAULT_LOG_LEVEL
) -> Tuple[bool, Optional[int]]:
    """
    添加任务到数据库
    返回：(是否成功, 任务ID/None)
    """
    conn = None
    try:
        # 先检查脚本是否存在
        if not get_script_by_id(script_id):
            logger.warning(f"关联脚本不存在：script_id={script_id}")
            return False, None

        # 计算下次运行时间
        next_run = calculate_next_run(cron_expression)

        conn = get_db_connection()
        cursor = conn.cursor()

        # 插入任务记录
        cursor.execute('''
        INSERT INTO tasks (
            name, script_id, cron_expression, is_enabled,
            record_screen, log_level, next_run
        ) VALUES (?, ?, ?, ?, ?, ?, ?)
        ''', (
            name, script_id, cron_expression,
            1 if is_enabled else 0, 1 if record_screen else 0,
            log_level, next_run
        ))

        conn.commit()
        task_id = cursor.lastrowid
        logger.info(f"添加任务成功：task_id={task_id}，名称={name}，Cron={cron_expression}")
        return True, task_id
    except sqlite3.Error as e:
        logger.error(f"添加任务失败：{str(e)}，名称={name}，script_id={script_id}")
        if conn:
            conn.rollback()
        return False, None
    finally:
        close_db_connection(conn)


def get_task_by_id(task_id: int) -> Optional[Dict[str, Any]]:
    """通过ID获取任务信息"""
    conn = None
    try:
        conn = get_db_connection()
        cursor = conn.cursor()

        cursor.execute('''
        SELECT 
            id, name, script_id, cron_expression, is_enabled,
            record_screen, log_level, created_at, last_run, next_run
        FROM tasks WHERE id = ?
        ''', (task_id,))

        task = cursor.fetchone()
        return dict(task) if task else None
    except sqlite3.Error as e:
        logger.error(f"获取任务失败：task_id={task_id}，错误：{str(e)}")
        return None
    finally:
        close_db_connection(conn)


def load_tasks(enabled_only: bool = False) -> List[Dict[str, Any]]:
    """
    加载任务列表（默认加载所有，enabled_only=True仅加载启用的）
    返回：任务列表（含关联脚本名称）
    """
    conn = None
    try:
        conn = get_db_connection()
        cursor = conn.cursor()

        # 构建查询条件
        where_clause = "WHERE t.is_enabled = 1" if enabled_only else ""

        # 关联脚本表，获取脚本名称
        cursor.execute(f'''
        SELECT 
            t.id, t.name, t.script_id, s.name AS script_name,
            t.cron_expression, t.is_enabled, t.record_screen,
            t.log_level, t.created_at, t.last_run, t.next_run
        FROM tasks t
        JOIN scripts s ON t.script_id = s.id
        {where_clause}
        ORDER BY t.name ASC
        ''')

        tasks = [dict(row) for row in cursor.fetchall()]
        logger.info(f"加载任务数量：{len(tasks)}（enabled_only={enabled_only}）")
        return tasks
    except sqlite3.Error as e:
        logger.error(f"加载任务失败：{str(e)}")
        return []
    finally:
        close_db_connection(conn)


def update_task(
        task_id: int, name: Optional[str] = None, cron_expression: Optional[str] = None,
        is_enabled: Optional[bool] = None, record_screen: Optional[bool] = None,
        log_level: Optional[str] = None
) -> bool:
    """更新任务信息（支持部分字段更新）"""
    conn = None
    try:
        conn = get_db_connection()
        cursor = conn.cursor()

        # 构建更新字段
        update_fields = []
        params = []
        if name is not None:
            update_fields.append("name = ?")
            params.append(name)
        if cron_expression is not None:
            update_fields.append("cron_expression = ?")
            params.append(cron_expression)
            # 同步更新下次运行时间
            update_fields.append("next_run = ?")
            params.append(calculate_next_run(cron_expression))
        if is_enabled is not None:
            update_fields.append("is_enabled = ?")
            params.append(1 if is_enabled else 0)
        if record_screen is not None:
            update_fields.append("record_screen = ?")
            params.append(1 if record_screen else 0)
        if log_level is not None:
            update_fields.append("log_level = ?")
            params.append(log_level)

        if not update_fields:
            logger.warning(f"无更新字段：task_id={task_id}")
            return True

        # 执行更新
        params.append(task_id)
        cursor.execute(f'''
        UPDATE tasks SET {', '.join(update_fields)}
        WHERE id = ?
        ''', params)

        if cursor.rowcount == 0:
            logger.warning(f"任务不存在或无变化：task_id={task_id}")
            return False

        conn.commit()
        logger.info(f"更新任务成功：task_id={task_id}，字段={update_fields}")
        return True
    except sqlite3.Error as e:
        logger.error(f"更新任务失败：task_id={task_id}，错误：{str(e)}")
        if conn:
            conn.rollback()
        return False
    finally:
        close_db_connection(conn)


def update_task_run_time(task_id: int, last_run: Optional[str] = None, next_run: Optional[str] = None) -> bool:
    """更新任务的最后运行时间和下次运行时间"""
    conn = None
    try:
        conn = get_db_connection()
        cursor = conn.cursor()

        update_fields = []
        params = []
        if last_run is not None:
            update_fields.append("last_run = ?")
            params.append(last_run)
        if next_run is not None:
            update_fields.append("next_run = ?")
            params.append(next_run)

        if not update_fields:
            return True

        params.append(task_id)
        cursor.execute(f'''
        UPDATE tasks SET {', '.join(update_fields)}
        WHERE id = ?
        ''', params)

        if cursor.rowcount == 0:
            logger.warning(f"任务不存在：task_id={task_id}")
            return False

        conn.commit()
        logger.info(f"更新任务运行时间：task_id={task_id}，last_run={last_run}，next_run={next_run}")
        return True
    except sqlite3.Error as e:
        logger.error(f"更新任务运行时间失败：task_id={task_id}，错误：{str(e)}")
        if conn:
            conn.rollback()
        return False
    finally:
        close_db_connection(conn)


def delete_task(task_id: int) -> bool:
    """删除任务（级联删除关联日志）"""
    conn = None
    try:
        conn = get_db_connection()
        cursor = conn.cursor()

        # 先获取任务名称
        cursor.execute("SELECT name FROM tasks WHERE id = ?", (task_id,))
        task = cursor.fetchone()
        task_name = task['name'] if task else f"ID={task_id}"

        # 执行删除
        cursor.execute("DELETE FROM tasks WHERE id = ?", (task_id,))

        if cursor.rowcount == 0:
            logger.warning(f"任务不存在：task_id={task_id}")
            return False

        conn.commit()
        logger.info(f"删除任务成功：task_id={task_id}，名称={task_name}")
        return True
    except sqlite3.Error as e:
        logger.error(f"删除任务失败：task_id={task_id}，错误：{str(e)}")
        if conn:
            conn.rollback()
        return False
    finally:
        close_db_connection(conn)


def get_script_path_by_task_id(task_id: int) -> Optional[str]:
    """通过任务ID获取关联脚本的路径"""
    conn = None
    try:
        conn = get_db_connection()
        cursor = conn.cursor()

        cursor.execute('''
        SELECT s.path FROM scripts s
        JOIN tasks t ON s.id = t.script_id
        WHERE t.id = ?
        ''', (task_id,))

        result = cursor.fetchone()
        return result['path'] if result else None
    except sqlite3.Error as e:
        logger.error(f"获取脚本路径失败：task_id={task_id}，错误：{str(e)}")
        return None
    finally:
        close_db_connection(conn)


# -------------------------- 任务日志表操作（CRUD） --------------------------
def add_task_log(task_id: int, start_time: str, status: str = "运行中") -> Tuple[bool, Optional[int]]:
    """添加任务运行日志（开始记录）"""
    conn = None
    try:
        conn = get_db_connection()
        cursor = conn.cursor()

        cursor.execute('''
        INSERT INTO task_logs (task_id, start_time, status)
        VALUES (?, ?, ?)
        ''', (task_id, start_time, status))

        conn.commit()
        log_id = cursor.lastrowid
        logger.info(f"添加任务日志：log_id={log_id}，task_id={task_id}，状态={status}")
        return True, log_id
    except sqlite3.Error as e:
        logger.error(f"添加任务日志失败：task_id={task_id}，错误：{str(e)}")
        if conn:
            conn.rollback()
        return False, None
    finally:
        close_db_connection(conn)


def update_task_log(
        log_id: int, end_time: Optional[str] = None, status: Optional[str] = None,
        log_content: Optional[str] = None, screen_recording_path: Optional[str] = None
) -> bool:
    """更新任务运行日志（结束记录）"""
    conn = None
    try:
        conn = get_db_connection()
        cursor = conn.cursor()

        update_fields = []
        params = []
        if end_time is not None:
            update_fields.append("end_time = ?")
            params.append(end_time)
        if status is not None:
            update_fields.append("status = ?")
            params.append(status)
        if log_content is not None:
            update_fields.append("log_content = ?")
            params.append(log_content)
        if screen_recording_path is not None:
            update_fields.append("screen_recording_path = ?")
            params.append(screen_recording_path)

        if not update_fields:
            logger.warning(f"无更新字段：log_id={log_id}")
            return True

        params.append(log_id)
        cursor.execute(f'''
        UPDATE task_logs SET {', '.join(update_fields)}
        WHERE id = ?
        ''', params)

        if cursor.rowcount == 0:
            logger.warning(f"日志不存在：log_id={log_id}")
            return False

        conn.commit()
        logger.info(f"更新任务日志：log_id={log_id}，字段={update_fields}")
        return True
    except sqlite3.Error as e:
        logger.error(f"更新任务日志失败：log_id={log_id}，错误：{str(e)}")
        if conn:
            conn.rollback()
        return False
    finally:
        close_db_connection(conn)


def get_task_log_by_id(log_id: int) -> Optional[Dict[str, Any]]:
    """通过ID获取任务日志详情"""
    conn = None
    try:
        conn = get_db_connection()
        cursor = conn.cursor()

        cursor.execute('''
        SELECT 
            id, task_id, start_time, end_time, status,
            log_content, screen_recording_path
        FROM task_logs WHERE id = ?
        ''', (log_id,))

        log = cursor.fetchone()
        return dict(log) if log else None
    except sqlite3.Error as e:
        logger.error(f"获取任务日志失败：log_id={log_id}，错误：{str(e)}")
        return None
    finally:
        close_db_connection(conn)


def load_task_logs(task_id: Optional[int] = None, start_time_after: Optional[str] = None) -> List[Dict[str, Any]]:
    """
    加载任务日志（支持筛选任务ID和开始时间）
    task_id: 可选，指定任务ID
    start_time_after: 可选，筛选该时间之后的日志
    """
    conn = None
    try:
        conn = get_db_connection()
        cursor = conn.cursor()

        # 构建查询条件
        where_clause = []
        params = []
        if task_id is not None:
            where_clause.append("tl.task_id = ?")
            params.append(task_id)
        if start_time_after is not None:
            where_clause.append("tl.start_time >= ?")
            params.append(start_time_after)

        where_sql = "WHERE " + " AND ".join(where_clause) if where_clause else ""

        # 关联任务表，获取任务名称
        cursor.execute(f'''
        SELECT 
            tl.id, tl.task_id, t.name AS task_name,
            tl.start_time, tl.end_time, tl.status,
            tl.log_content, tl.screen_recording_path
        FROM task_logs tl
        JOIN tasks t ON tl.task_id = t.id
        {where_sql}
        ORDER BY tl.start_time DESC
        ''', params)

        logs = [dict(row) for row in cursor.fetchall()]
        logger.info(f"加载任务日志数量：{len(logs)}（task_id={task_id}，start_time_after={start_time_after}）")
        return logs
    except sqlite3.Error as e:
        logger.error(f"加载任务日志失败：{str(e)}")
        return []
    finally:
        close_db_connection(conn)


def get_expired_logs(cutoff_time: str) -> List[Dict[str, Any]]:
    """获取过期日志（指定时间之前的日志）"""
    conn = None
    try:
        conn = get_db_connection()
        cursor = conn.cursor()

        cursor.execute('''
        SELECT id, screen_recording_path
        FROM task_logs
        WHERE start_time < ?
        ''', (cutoff_time,))

        logs = [dict(row) for row in cursor.fetchall()]
        logger.info(f"获取过期日志数量：{len(logs)}（截止时间：{cutoff_time}）")
        return logs
    except sqlite3.Error as e:
        logger.error(f"获取过期日志失败：{str(e)}")
        return []
    finally:
        close_db_connection(conn)


def delete_log_by_id(log_id: int) -> bool:
    """通过ID删除任务日志"""
    conn = None
    try:
        conn = get_db_connection()
        cursor = conn.cursor()

        cursor.execute("DELETE FROM task_logs WHERE id = ?", (log_id,))

        if cursor.rowcount == 0:
            logger.warning(f"日志不存在：log_id={log_id}")
            return False

        conn.commit()
        logger.info(f"删除任务日志：log_id={log_id}")
        return True
    except sqlite3.Error as e:
        logger.error(f"删除任务日志失败：log_id={log_id}，错误：{str(e)}")
        if conn:
            conn.rollback()
        return False
    finally:
        close_db_connection(conn)
